# 技术运维下四项数据导出
import datetime

from other.大正.utils.CsvUtils import exportCsvLocalByDataList
from other.大正.utils.DbConnUtil import executeSql


# 查询2018-08-01之前的数据
def queryBefore201808(startTime, endTime, workTime):
    sql = '''
        SELECT
			sum( IF ( t2.f_work_status = 1, 1, 0 ) ) 'status1',
			round(sum(IF(t2.f_work_status = 1,TIMESTAMPDIFF(MINUTE,IF( t2.f_startTime >{_startTime}, t2.f_startTime,{_startTime} ),IF( t2.f_endTime IS NULL, {_endTime}, 
			IF ( t2.f_endTime < {_endTime}, t2.f_endTime, {_endTime} ))),0))/60/24,2) 'status1_day',
			sum( IF ( t2.f_work_status = 2 OR t2.f_work_status = 3, 1, 0 ) ) 'status23',
			round(sum(IF(t2.f_work_status = 2 OR t2.f_work_status = 3,TIMESTAMPDIFF(MINUTE,IF( t2.f_startTime >{_startTime}, t2.f_startTime,{_startTime} ),
			IF( t2.f_endTime IS NULL, {_endTime}, IF ( t2.f_endTime < {_endTime}, t2.f_endTime, {_endTime} ))),0))/60/24,2) 'status23_day'
        FROM
        (
            SELECT
                f_id
            FROM
                t_factory
            WHERE
                f_status = 0
            AND f_work_start_time <{_f_work_time}
        ) t1
            LEFT JOIN t_factory_business t2 ON t1.f_id = t2.f_factory_id
            AND (
            ( t2.f_startTime >={_startTime} AND t2.f_startTime < {_endTime} )
            OR ( t2.f_endTime >={_startTime} AND t2.f_endTime < {_endTime} )
            OR ( t2.f_startTime <{_startTime} AND t2.f_endTime >= {_endTime} )
        )
    '''.format(_startTime=repr(startTime),
               _endTime=repr(endTime),
               _f_work_time=repr(workTime))
    result = executeSql(sql)
    return result


# 查询2018-08-01之后的数据
def query(startTime, endTime, workStartTime, workEndTime):
    sql = '''
        SELECT
			sum( IF ( t2.f_work_status = 1, 1, 0 ) ) 'status1',
			round(sum(IF(t2.f_work_status = 1,TIMESTAMPDIFF(MINUTE,IF( t2.f_startTime >{_startTime}, t2.f_startTime,{_startTime} ),IF( t2.f_endTime IS NULL, {_endTime}, 
			IF ( t2.f_endTime < {_endTime}, t2.f_endTime, {_endTime} ))),0))/60/24,2) 'status1_day',
			sum( IF ( t2.f_work_status = 2 OR t2.f_work_status = 3, 1, 0 ) ) 'status23',
			round(sum(IF(t2.f_work_status = 2 OR t2.f_work_status = 3,TIMESTAMPDIFF(MINUTE,IF( t2.f_startTime >{_startTime}, t2.f_startTime,{_startTime} ),
			IF( t2.f_endTime IS NULL, {_endTime}, IF ( t2.f_endTime < {_endTime}, t2.f_endTime, {_endTime} ))),0))/60/24,2) 'status23_day'
        FROM
        (
            SELECT
                f_id
            FROM
                t_factory
            WHERE
                f_status = 0
            AND f_work_start_time >= {_f_work_start_time}
            AND f_work_start_time <{_f_work_end_time}
        ) t1
            LEFT JOIN t_factory_business t2 ON t1.f_id = t2.f_factory_id
            AND (
            ( t2.f_startTime >={_startTime} AND t2.f_startTime < {_endTime} )
            OR ( t2.f_endTime >={_startTime} AND t2.f_endTime < {_endTime} )
            OR ( t2.f_startTime <{_startTime} AND t2.f_endTime >= {_endTime} )
        )
    '''.format(_startTime=repr(startTime),
               _endTime=repr(endTime),
               _f_work_start_time=repr(workStartTime),
               _f_work_end_time=repr(workEndTime))
    result = executeSql(sql)
    return result


# 构造数据集
def buildDataList(dataList, result, dateTime):
    rowData = []
    rowData.append(dateTime)
    rowData.append(repr(result["status1"][0]))  # 故障次数（现场维修）
    rowData.append(repr(result["status1_day"][0]))  # 机器故障引起的停工时间（现场维修）
    rowData.append(repr(result["status23"][0]))  # 渠道停工次数
    rowData.append(repr(result["status23_day"][0]))  # 渠道停工时间
    dataList.append(rowData)


# ===========================   开始执行查询逻辑  ================================= #
# 横轴时间范围
startTime = '2022-10-01'
endTime = '2022-11-01'
# 竖轴时间范围
work_start_time = '2018-08-01'
work_end_time = startTime

# 声明一些参数集，存储查询的结果
dataList = []

# 先查一下 2018-08-01之前的数据
result = queryBefore201808(startTime, endTime, '2018-08-01')
buildDataList(dataList, result, '2018-08-01之前')

# 再查一下 2018-08-01之后的数据
while work_start_time != work_end_time:
    reduceOneDay = (datetime.datetime.strptime(work_start_time, '%Y-%m-%d') + datetime.timedelta(days=32)).replace(
        day=1)
    result = query(startTime, endTime, work_start_time, reduceOneDay.strftime("%Y-%m-%d"))

    buildDataList(dataList, result, work_start_time)
    work_start_time = reduceOneDay.strftime('%Y-%m-%d')

# 生成本地文件
columnNames = ['竖轴日期', '故障次数（现场维修）', '机器故障引起的停工时间（现场维修）', '渠道停工次数', '渠道停工时间']
exportCsvLocalByDataList(rf"D:\Store\Python-Download\下四项数据22{startTime}.csv", dataList, columnNames)
print('数据导出结束...')
